package com.xjgc.ws.general.dao;

import com.xjgc.common.config.DbConn;
import com.xjgc.common.utils.NumberUtil;
import com.xjgc.ws.util.CheckData;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Iterator;

@Component
public class Zxjzsdtstr003 {
    @Autowired
    DbConn db;

    public String work(String xmlData) throws SQLException {

        String rs = null;
        PreparedStatement inPst = null;
        PreparedStatement delPst = null;

        //定义执行SQL脚本
        String inSql = "insert into zxj_zsdtstr003 values(?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";  //85插入SQL

        String delSql = "delete from zxj_zsdtstr003 where \"VKORG\"=? and \"VBELN \"=? and \"VTEXT\" =?"; //删除SQL
        //创建数据库连接
        inPst = db.getConnetion().prepareStatement(inSql);
        delPst = db.getConnetion().prepareStatement(delSql);
        int count = 0;
        //解析xml文件
        Document doc = null;

        try {
            doc = DocumentHelper.parseText(xmlData);
            Element root = doc.getRootElement();
            Iterator<?> it = root.elementIterator();
            while (it.hasNext()) {

                Element element = (Element) it.next();
                delPst.setString(1,element.elementText("VKORG"));
                delPst.setString(2,element.elementText("VBELN "));
                delPst.setString(3,element.elementText("VTEXT"));

                inPst.setString(1, element.elementText("VKORG"));
                inPst.setString(2, element.elementText("VBELN"));
                inPst.setString(3, element.elementText("VTEXT"));
                inPst.setString(4, element.elementText("VTEXT1"));
                inPst.setString(5, element.elementText("SPART"));
                inPst.setString(6, element.elementText("VTEXT2"));
                inPst.setString(7, element.elementText("BSTKD_E"));
                inPst.setString(8, element.elementText("AUART"));
                inPst.setString(9, element.elementText("HKFSWB"));

                String vdatu = element.elementText("VDATU");
                java.util.Date date1 = new SimpleDateFormat("yyyyMMdd").parse(vdatu);
                inPst.setDate(10, new Date(date1.getTime()));

                inPst.setString(11, element.elementText("BEZEI"));
                inPst.setString(12, element.elementText("BEZEI1"));
                inPst.setString(13, element.elementText("BSTKD"));
                inPst.setString(14, element.elementText("KUNNR1"));
                inPst.setString(15, element.elementText("NAME1"));
                inPst.setString(16, element.elementText("KUNNR"));
                inPst.setString(17, element.elementText("NAME2"));
                inPst.setString(18, element.elementText("BNAME"));

                inPst.setInt(19, Integer.valueOf(element.elementText("HKFZR")));

                inPst.setString(20, element.elementText("ENAME"));

                inPst.setInt(21, Integer.valueOf(element.elementText("HTQDR")));

                inPst.setString(22, element.elementText("ENAME1"));
                inPst.setString(23, element.elementText("HTJE"));

                inPst.setBigDecimal(24, NumberUtil.negate(element.elementText("HTZJE_A1")));
                inPst.setBigDecimal(25, NumberUtil.negate(element.elementText("HKZJE")));
                inPst.setBigDecimal(26, NumberUtil.negate(element.elementText("ZHK")));

                inPst.setString(27, element.elementText("HKBL"));

                inPst.setBigDecimal(28, NumberUtil.negate(element.elementText("HTYE")));

                inPst.setBigDecimal(29, NumberUtil.negate(element.elementText("YFHJE")));

                inPst.setBigDecimal(30, NumberUtil.negate(element.elementText("DQYHZE")));

                inPst.setBigDecimal(31, NumberUtil.negate(element.elementText("YQZE")));

                String erdat = element.elementText("ERDAT");
                java.util.Date date = new SimpleDateFormat("yyyyMMdd").parse(erdat);
                inPst.setDate(32, new Date(date.getTime()));
                inPst.setString(33, element.elementText("YFKBL"));


                inPst.setBigDecimal(34, NumberUtil.negate(element.elementText("YFK")));

                String yfkdqsj = element.elementText("YFKDQSJ");
                java.util.Date date2 = new SimpleDateFormat("yyyyMMdd").parse(yfkdqsj);
                inPst.setDate(35, new Date(date2.getTime()));

                inPst.setBigDecimal(36, NumberUtil.negate(element.elementText("YFKYQJE")));

                inPst.setString(37, element.elementText("YFKYQSJ"));
                inPst.setString(38, element.elementText("DHKBL"));

                inPst.setBigDecimal(39, NumberUtil.negate(element.elementText("JDK")));

                String jdrq = element.elementText("JDRQ");
                java.util.Date date3 = new SimpleDateFormat("yyyyMMdd").parse(jdrq);
                inPst.setDate(40, new Date(date3.getTime()));

                String jddqsj = element.elementText("JDDQSJ");
                java.util.Date date4 = new SimpleDateFormat("yyyyMMdd").parse(jddqsj);
                inPst.setDate(41, new Date(date4.getTime()));

                inPst.setBigDecimal(42, NumberUtil.negate(element.elementText("JDYQJE")));

                inPst.setString(43, element.elementText("JDYQSJ"));
                inPst.setString(44, element.elementText("JDBL"));

                inPst.setBigDecimal(45, NumberUtil.negate(element.elementText("DHK")));

                String fhsj = element.elementText("FHSJ");
                java.util.Date date5 = new SimpleDateFormat("yyyyMMdd").parse(fhsj);
                inPst.setDate(46, new Date(date5.getTime()));

                inPst.setBigDecimal(47, NumberUtil.negate(element.elementText("DHKYQJE")));

                inPst.setString(48, element.elementText("DHKDQSJ"));
                inPst.setString(49, element.elementText("DHKYQSJ"));
                inPst.setString(50, element.elementText("YXKBL"));

                inPst.setBigDecimal(51, NumberUtil.negate(element.elementText("YXK")));

                String zdat_ty = element.elementText("ZDAT_TY");
                java.util.Date date6 = new SimpleDateFormat("yyyyMMdd").parse(zdat_ty);
                inPst.setDate(52, new Date(date6.getTime()));


                inPst.setBigDecimal(53, NumberUtil.negate(element.elementText("YXKYQJE")));

                String tykdqsj = element.elementText("TYKDQSJ");
                java.util.Date date7 = new SimpleDateFormat("yyyyMMdd").parse(tykdqsj);
                inPst.setDate(54, new Date(date7.getTime()));

                inPst.setString(55, element.elementText("YXKYQSJ"));
                inPst.setString(56, element.elementText("ZBKBL"));

                inPst.setBigDecimal(57,  NumberUtil.negate(element.elementText("ZBK")));

                String zbrq = element.elementText("ZBRQ");
                java.util.Date date8 = new SimpleDateFormat("yyyyMMdd").parse(zbrq);
                inPst.setDate(58, new Date(date8.getTime()));

                inPst.setBigDecimal(59, NumberUtil.negate(element.elementText("ZBKYQJE")));

                inPst.setString(60, element.elementText("ZBKYQSJ"));

                inPst.setBigDecimal(61, NumberUtil.negate(element.elementText("ZZSFPKJ")));

                inPst.setBigDecimal(62, NumberUtil.negate(element.elementText("KPKYQJE")));

                inPst.setString(63, element.elementText("KPKYQSJ"));

                String zhhkrq = element.elementText("ZHHKRQ");
                java.util.Date date9 = new SimpleDateFormat("yyyyMMdd").parse(zhhkrq);
                inPst.setDate(64, new Date(date9.getTime()));

                String kprq = element.elementText("KPRQ");
                java.util.Date date10 = new SimpleDateFormat("yyyyMMdd").parse(kprq);
                inPst.setDate(65, new Date(date10.getTime()));

                inPst.setBigDecimal(66, NumberUtil.negate(element.elementText("KPJE")));

                inPst.setString(67, element.elementText("ZQKYY_T"));
                inPst.setString(68, element.elementText("QKYY"));
                inPst.setString(69, element.elementText("ZWZWYM"));
                inPst.setString(70, element.elementText("TEXT"));
                inPst.setString(71, element.elementText("FKSM"));

                String cdate = element.elementText("CDATE");
                java.util.Date date11 = new SimpleDateFormat("yyyyMMdd").parse(cdate);
                inPst.setDate(72, new Date(date11.getTime()));


                inPst.setString(73, element.elementText("HKFSSM"));
                inPst.setString(74, element.elementText("TVAUT"));
                inPst.setString(75, element.elementText("BSTZD"));

                inPst.setBigDecimal(76, NumberUtil.negate(element.elementText("YFKYSJE")));

                inPst.setBigDecimal(77, NumberUtil.negate(element.elementText("JDYSJE")));

                inPst.setBigDecimal(78, NumberUtil.negate(element.elementText("DHKYSJE")));

                inPst.setBigDecimal(79, NumberUtil.negate(element.elementText("YXKYSJE")));

                inPst.setBigDecimal(80, NumberUtil.negate(element.elementText("ZBKYSJE")));

                String yfkyqrq = element.elementText("YFKYQRQ");
                java.util.Date date12 = new SimpleDateFormat("yyyyMMdd").parse(yfkdqsj);
                inPst.setDate(81, new Date(date12.getTime()));

                String jdkyqrq = element.elementText("JDKYQRQ");
                java.util.Date date13 = new SimpleDateFormat("yyyyMMdd").parse(jdkyqrq);
                inPst.setDate(82, new Date(date13.getTime()));

                String dhkyqrq = element.elementText("DHKYQRQ");
                java.util.Date date14 = new SimpleDateFormat("yyyyMMdd").parse(dhkyqrq);
                inPst.setDate(83, new Date(date14.getTime()));

                String yxkyqrq = element.elementText("YXKYQRQ");
                java.util.Date date15 = new SimpleDateFormat("yyyyMMdd").parse(yxkyqrq);
                inPst.setDate(84, new Date(date15.getTime()));

                String zbkyqrq = element.elementText("ZBKYQRQ");
                java.util.Date date16 = new SimpleDateFormat("yyyyMMdd").parse(zbkyqrq);
                inPst.setDate(85, new Date(date16.getTime()));

                //删除更新数据
                delPst.executeUpdate();

                //插入更新数据
                int row = inPst.executeUpdate();
                if (row > 0) {
                    count++;
                }
            }
            if (count > 0) {
                rs = "0";//执行成功
            }
        } catch(DocumentException | ParseException e1) {
            rs = e1.toString();//执行失败
            e1.printStackTrace();
        }

        //关闭连接
        delPst.close();
        inPst.close();
        db.closeCn();
        return rs;
        }
}
